package net.web.dao;

import net.web.common.utils.JdbcUtils;
import net.web.common.utils.PageInfo;
import net.web.entity.User;

import java.util.ArrayList;
import java.util.List;

public class UserDao {

    public PageInfo<User> pagelist(int pageno, int pagesize, User user) {
        StringBuilder sql = new StringBuilder(
                "select id, username, password, email, userPicture, bio, role, likedArticles, collectedArticles, createdAt, updatedAt " +
                        "from user where 1=1 "
        );
        List<Object> params = new ArrayList<>();

        if (user.getUsername() != null && !user.getUsername().isEmpty()) {
            sql.append(" and username like ? ");
            params.add("%" + user.getUsername() + "%");
        }
        if (user.getEmail() != null && !user.getEmail().isEmpty()) {
            sql.append(" and email like ? ");
            params.add("%" + user.getEmail() + "%");
        }
        if (user.getRole() != -1) {
            sql.append(" and role = ? ");
            params.add(user.getRole());
        }

        sql.append(" order by createdAt desc limit ?, ? ");
        params.add((pageno - 1) * pagesize);
        params.add(pagesize);

        List<User> records = JdbcUtils.executeQuery(User.class, sql.toString(), params.toArray());

        StringBuilder countSql = new StringBuilder("select count(*) from user where 1=1 ");
        List<Object> countParams = new ArrayList<>();
        if (user.getUsername() != null && !user.getUsername().isEmpty()) {
            countSql.append(" and username like ? ");
            countParams.add("%" + user.getUsername() + "%");
        }
        if (user.getEmail() != null && !user.getEmail().isEmpty()) {
            countSql.append(" and email like ? ");
            countParams.add("%" + user.getEmail() + "%");
        }
        if (user.getRole() != -1) {
            countSql.append(" and role = ? ");
            countParams.add(user.getRole());
        }
        long total = JdbcUtils.executeQueryOneValue(countSql.toString(), countParams.toArray());

        return new PageInfo<>(pageno, pagesize, (int) total, records);
    }

    public List<User> list() {
        return JdbcUtils.executeQuery(User.class,
                "select id, username, email, role from user order by createdAt desc");
    }

    public boolean insert(User user) {
        String sql = "insert into user(username, password, email, userPicture, bio, role, likedArticles, collectedArticles, createdAt, updatedAt) " +
                "values (?,?,?,?,?,?,?,?,?,?)";
        String now = JdbcUtils.now();
        return JdbcUtils.executeUpdate(sql,
                user.getUsername(), user.getPassword(), user.getEmail(),
                user.getUserPicture(), user.getBio(), user.getRole(),
                user.getLikedArticles(), user.getCollectedArticles(),
                now, now) > 0;
    }

    public boolean delete(int id) {
        return JdbcUtils.executeUpdate("delete from user where id = ?", id) > 0;
    }

    public boolean update(User user) {
        String sql = "update user set username=?, email=?, userPicture=?, bio=?, role=?, likedArticles=?, collectedArticles=?, updatedAt=? where id=?";
        String now = JdbcUtils.now();
        return JdbcUtils.executeUpdate(sql,
                user.getUsername(), user.getEmail(), user.getUserPicture(),
                user.getBio(), user.getRole(), user.getLikedArticles(),
                user.getCollectedArticles(), now, user.getId()) > 0;
    }

    public boolean updateProfile(User user) {
        String sql = "update user set email=?, userPicture=?, bio=?, updatedAt=? where id=?";
        String now = JdbcUtils.now();
        return JdbcUtils.executeUpdate(sql,
                user.getEmail(), user.getUserPicture(), user.getBio(),
                now, user.getId()) > 0;
    }

    public boolean updatePassword(int userId, String newPassword) {
        String sql = "update user set password=?, updatedAt=? where id=?";
        String now = JdbcUtils.now();
        return JdbcUtils.executeUpdate(sql, newPassword, now, userId) > 0;
    }

    public User findById(int id) {
        List<User> users = JdbcUtils.executeQuery(User.class,
                "select id, username, password, email, userPicture, bio, role, likedArticles, collectedArticles, createdAt, updatedAt from user where id = ?", id);
        return users.isEmpty() ? null : users.get(0);
    }

    public User findByUsername(String username) {
        List<User> users = JdbcUtils.executeQuery(User.class,
                "select id, username, password, email, userPicture, bio, role, likedArticles, collectedArticles, createdAt, updatedAt from user where username = ?", username);
        return users.isEmpty() ? null : users.get(0);
    }

    public User findByEmail(String email) {
        List<User> users = JdbcUtils.executeQuery(User.class,
                "select id, username, password, email, userPicture, bio, role, likedArticles, collectedArticles, createdAt, updatedAt from user where email = ?", email);
        return users.isEmpty() ? null : users.get(0);
    }

    public User findByUsernameOrEmail(String loginName) {
        List<User> users = JdbcUtils.executeQuery(User.class,
                "select id, username, password, email, userPicture, bio, role, likedArticles, collectedArticles, createdAt, updatedAt from user where username = ? or email = ?",
                loginName, loginName);
        return users.isEmpty() ? null : users.get(0);
    }

    public boolean existsByUsername(String username) {
        Long count = JdbcUtils.executeQueryOneValue("select count(*) from user where username = ?", username);
        return count != null && count > 0;
    }

    public boolean existsByEmail(String email) {
        Long count = JdbcUtils.executeQueryOneValue("select count(*) from user where email = ?", email);
        return count != null && count > 0;
    }

    public boolean existsByUsernameExcludeId(String username, int excludeId) {
        Long count = JdbcUtils.executeQueryOneValue("select count(*) from user where username = ? and id != ?", username, excludeId);
        return count != null && count > 0;
    }

    public boolean existsByEmailExcludeId(String email, int excludeId) {
        Long count = JdbcUtils.executeQueryOneValue("select count(*) from user where email = ? and id != ?", email, excludeId);
        return count != null && count > 0;
    }
}
